package TeachLesson.Res;

import java.rmi.server.ExportException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class ResDao {

    public Connection getCon(){
        Connection con=null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url="jdbc:mysql://localhost:3306/jwglxt?serverTimezone=UTC&characterEncoding=utf-8";
            con= DriverManager.getConnection(url,"root","root");
        }catch (Exception e){
            e.printStackTrace();
        }
        return con;
    }

    public int addRes(String workerNo,String courseNo,String fileRoot,String fileName){
        Connection con=this.getCon();
        try {
            String sql="insert into tb_res (workerNo,courseNo,fileRoot, fileName) values (?,?,?,?)";
            PreparedStatement ps=con.prepareStatement(sql);
            ps.setString(1,workerNo);
            ps.setString(2,courseNo);
            ps.setString(3,fileRoot);
            ps.setString(4,fileName);
            int row=ps.executeUpdate();
            ps.close();
            con.close();
            return row;
        }catch (Exception e){
            e.printStackTrace();
            return 0;
        }
    }




    public ArrayList<ResEnity> getRes(String fileRoot){
        Connection connection=this.getCon();
        try {
            String sql="select * from tb_res where fileRoot=?";
            PreparedStatement ps=connection.prepareStatement(sql);
            ps.setString(1,fileRoot);
            ResultSet rs=ps.executeQuery();
            ArrayList<ResEnity> list=new ArrayList<>();
            while (rs.next()){
                ResEnity resEnity=new ResEnity();
                resEnity.setId(rs.getInt("id"));
                resEnity.setCourseNo(rs.getString("courseNo"));
                resEnity.setWorkerNo(rs.getString("workerNo"));
                resEnity.setFileRoot(rs.getString("fileRoot"));
                resEnity.setFileName(rs.getString("fileName"));
                list.add(resEnity);
            }
            rs.close();
            ps.close();
            connection.close();
            return list;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }

    }


    //依据ID
    public ResEnity getRes(int id){
        Connection connection=this.getCon();
        try {
            String sql="select * from tb_res where id=?";
            PreparedStatement ps=connection.prepareStatement(sql);
            ps.setInt(1,id);
            ResultSet rs=ps.executeQuery();
            ResEnity resEnity=new ResEnity();
            while (rs.next()){
                resEnity.setId(rs.getInt("id"));
                resEnity.setCourseNo(rs.getString("courseNo"));
                resEnity.setWorkerNo(rs.getString("workerNo"));
                resEnity.setFileRoot(rs.getString("fileRoot"));
                resEnity.setFileName(rs.getString("fileName"));

            }
            rs.close();
            ps.close();
            connection.close();
            return resEnity;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }

    }

    //删除文件
    public int deleteRes(int id){
        Connection con=this.getCon();
        try {
            String sql="delete from tb_res where id=?";
            PreparedStatement ps=con.prepareStatement(sql);
            ps.setInt(1,id);
            int row=ps.executeUpdate();
            ps.close();
            con.close();
            return row;
        }catch (Exception e){
            e.printStackTrace();
            return 0;
        }
    }
}
